﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HCommon
{
    public class Dao
    {
        //public static List<Param> lstParam;
        //--------------- Kết nối Database
        /// <summary>
        /// Biến static SqlConnection
        /// </summary>
        public static SqlConnection objConn = new SqlConnection();

        /// <summary>
        /// Hàm để kết nối database, conn truyền vào nên là biến static
        /// </summary>
        /// <param name="conn">SqlConnection truyền vào</param>
        /// <param name="connStr">Chuỗi kết nối truyền vào có dạng "server=.;User ID=sa;Password=123456;database=iTN;Connection Reset=FALSE"</param>
        public static bool Get_Connect(SqlConnection conn, string connStr, ref string errMsg)
        {
            conn = new SqlConnection(connStr);
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                if (conn.State != ConnectionState.Open)
                {
                    errMsg = "Không kết nối được tới CSDL";
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Connect", ex);
                errMsg = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// Hàm để kết nối database, gán kết nối vào kết nối static có sẵn DAO.objConn
        /// </summary>
        /// <param name="connStr">Chuỗi kết nối truyền vào có dạng "server=.;User ID=sa;Password=123456;database=iTN;Connection Reset=FALSE"</param>
        public static bool Get_Connect(string connStr, ref string errMsg)
        {
            objConn = new SqlConnection(connStr);
            try
            {
                if (objConn.State != ConnectionState.Open)
                {
                    objConn.Open();
                }
                if (objConn.State != ConnectionState.Open)
                {
                    errMsg = "Không kết nối được tới CSDL";
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Connect", ex);
                errMsg = ex.Message;
                return false;
            }
        }


        //--------------- SELECT DATABASE ---------------//
        //------- Truyền vào kết nối
        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, SqlConnection conn)
        {
            DataTable dt = new DataTable();
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlDataAdapter da = new SqlDataAdapter(TSQL, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Table", ex);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return dt;
        }

        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, SqlConnection conn, bool isClose)
        {
            DataTable dt = new DataTable();
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlDataAdapter da = new SqlDataAdapter(TSQL, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Table", ex);
            }
            finally
            {
                if (isClose)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, SqlConnection conn, SqlTransaction objTrans)
        {
            DataTable dt = new DataTable();
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlDataAdapter da = new SqlDataAdapter(TSQL, conn);
                da.SelectCommand.Transaction = objTrans;
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Table", ex);
            }
            return dt;
        }


        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <param name="da">Trả về DataAdapter dùng để update DataTable</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, SqlConnection conn, ref SqlDataAdapter da)
        {
            DataTable dt = new DataTable();
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                da = new SqlDataAdapter(TSQL, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Table", ex);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return dt;
        }

        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <param name="da">Trả về DataAdapter dùng để update DataTable</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, SqlConnection conn, ref SqlDataAdapter da, bool isClose)
        {
            DataTable dt = new DataTable();
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                da = new SqlDataAdapter(TSQL, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Table", ex);
            }
            finally
            {
                if (isClose)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <param name="da"></param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, SqlConnection conn, ref SqlDataAdapter da, SqlTransaction objTrans)
        {
            DataTable dt = new DataTable();
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                da = new SqlDataAdapter(TSQL, conn);
                da.SelectCommand.Transaction = objTrans;
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                Common.GhiLog("Get_Table", ex);
            }
            return dt;
        }



        //------- Không truyền vào kết nối
        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL)
        {
            return Get_Table(TSQL, objConn);
        }

        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, bool isClose)
        {
            return Get_Table(TSQL, objConn, isClose);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, SqlTransaction objTrans)
        {
            return Get_Table(TSQL, objConn, objTrans);
        }


        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <param name="da">Trả về DataAdapter dùng để update DataTable</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, ref SqlDataAdapter da)
        {
            return Get_Table(TSQL, objConn, ref da);
        }

        /// <summary>
        /// Lấy ra giá trị bảng khi Select dữ liệu trong SQL
        /// </summary>
        /// <param name="TSQL">Truyền vào câu lệnh Select</param>
        /// <param name="conn">Truyền vào kết nối SQL</param>
        /// <param name="da">Trả về DataAdapter dùng để update DataTable</param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, ref SqlDataAdapter da, bool isClose)
        {
            return Get_Table(TSQL, objConn, ref da, isClose);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <param name="da"></param>
        /// <returns></returns>
        public static DataTable Get_Table(string TSQL, ref SqlDataAdapter da, SqlTransaction objTrans)
        {
            return Get_Table(TSQL, objConn, ref da, objTrans);
        }





        //--------------- EXECUTE DATABASE ---------------//
        //------- Truyền vào kết nối
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int Exec_SQL(string TSQL, SqlConnection conn)
        {
            int rs = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(TSQL, conn);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSQL", TSQL, ex);
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int Exec_SQL(string TSQL, SqlConnection conn, bool isClose)
        {
            int rs = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(TSQL, conn);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSQL", TSQL, ex);
                throw ex;
            }
            finally
            {
                if (isClose)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <param name="objTrans"></param>
        /// <returns></returns>
        public static int Exec_SQL(string TSQL, SqlConnection conn, SqlTransaction objTrans)
        {
            int rs = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(TSQL, conn);
                cmd.Transaction = objTrans;
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSQL", TSQL, ex);
                throw ex;
            }
            //finally
            //{
            //    if (conn.State != ConnectionState.Closed)
            //    {
            //        conn.Close();
            //    }
            //}
        }

        // Hàm trả về id identity
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int ExecSca_SQL(string TSQL, SqlConnection conn)
        {
            int rs = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(TSQL, conn);
                object ID = cmd.ExecuteScalar();
                return int.Parse(ID.ToString());
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSca_SQL", TSQL, ex);
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int ExecSca_SQL(string TSQL, SqlConnection conn, bool isClose)
        {
            int rs = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(TSQL, conn);
                object ID = cmd.ExecuteScalar();
                return int.Parse(ID.ToString());
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSQL", TSQL, ex);
                throw ex;
            }
            finally
            {
                if (isClose)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <param name="objTrans"></param>
        /// <returns></returns>
        public static int ExecSca_SQL(string TSQL, SqlConnection conn, SqlTransaction objTrans)
        {
            int rs = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(TSQL, conn);
                cmd.Transaction = objTrans;
                object ID = cmd.ExecuteScalar();
                return int.Parse(ID.ToString());
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSQL", TSQL, ex);
                throw ex;
            }
            //finally
            //{
            //    if (conn.State != ConnectionState.Closed)
            //    {
            //        conn.Close();
            //    }
            //}
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="conn"></param>
        /// <param name="lstParam"></param>
        /// <returns></returns>
        public static int Exec_SP(string SPName, SqlConnection conn, List<Param> lstParam)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(SPName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (Param p in lstParam)
                {
                    cmd.Parameters.AddWithValue(p.ParamName, p.Value);
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSP", ex);
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="conn"></param>
        /// <param name="lstParam"></param>
        /// <returns></returns>
        public static int Exec_SP(string SPName, SqlConnection conn, List<Param> lstParam, bool isClose)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(SPName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (Param p in lstParam)
                {
                    cmd.Parameters.AddWithValue(p.ParamName, p.Value);
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSP", ex);
                throw ex;
            }
            finally
            {
                if (isClose)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="conn"></param>
        /// <param name="lstParam"></param>
        /// <returns></returns>
        public static string Exec_SP(string SPName, SqlConnection conn, List<Param> lstParam, SqlTransaction objTrans)
        {
            string MSG = "";
            SqlDataReader reader = null;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(SPName, conn);
                cmd.Transaction = objTrans;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (Param p in lstParam)
                {
                    cmd.Parameters.AddWithValue(p.ParamName, p.Value);
                }
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    MSG = reader["MSG"].ToString();
                }

            }
            catch (Exception ex)
            {
                Common.GhiLog("ExecSP", ex);
            }
            finally
            {
                if (!reader.IsClosed && reader != null)
                {
                    reader.Close();
                }
                //if (conn.State != ConnectionState.Closed)
                //{
                //    conn.Close();
                //}
            }
            return MSG;

        }



        //------- Không truyền vào kết nối
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int Exec_SQL(string TSQL)
        {
            return Exec_SQL(TSQL, objConn);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int Exec_SQL(string TSQL, bool isClose)
        {
            return Exec_SQL(TSQL, objConn, isClose);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <param name="objTrans"></param>
        /// <returns></returns>
        public static int Exec_SQL(string TSQL, SqlTransaction objTrans)
        {
            return Exec_SQL(TSQL, objConn, objTrans);
        }


        //------- Không truyền vào kết nối
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int ExecSca_SQL(string TSQL)
        {
            return ExecSca_SQL(TSQL, objConn);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static int ExecSca_SQL(string TSQL, bool isClose)
        {
            return ExecSca_SQL(TSQL, objConn, isClose);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TSQL"></param>
        /// <param name="conn"></param>
        /// <param name="objTrans"></param>
        /// <returns></returns>
        public static int ExecSca_SQL(string TSQL, SqlTransaction objTrans)
        {
            return ExecSca_SQL(TSQL, objConn, objTrans);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="conn"></param>
        /// <param name="lstParam"></param>
        /// <returns></returns>
        public static int Exec_SP(string SPName, List<Param> lstParam)
        {
            return Exec_SP(SPName, objConn, lstParam);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="conn"></param>
        /// <param name="lstParam"></param>
        /// <returns></returns>
        public static int Exec_SP(string SPName, List<Param> lstParam, bool isClose)
        {
            return Exec_SP(SPName, objConn, lstParam, isClose);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SPName"></param>
        /// <param name="conn"></param>
        /// <param name="lstParam"></param>
        /// <returns></returns>
        public static string Exec_SP(string SPName, List<Param> lstParam, SqlTransaction objTrans)
        {
            return Exec_SP(SPName, objConn, lstParam, objTrans);
        }




        //--------------- CHECK EXIST DATABASE ---------------//

        public static bool Exist(string TSQL, SqlConnection conn)
        {
            bool rs = false;
            try
            {
                DataTable dt = new DataTable();
                dt = Get_Table(TSQL, conn);
                if (dt.Rows.Count > 0)
                {
                    rs = true;
                }
            }
            catch (Exception ex)
            {
                rs = false;
                Common.GhiLog("Exist", ex);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return rs;
        }

        public static bool Exist(string TSQL, SqlConnection conn, bool isClose)
        {
            bool rs = false;
            try
            {
                DataTable dt = new DataTable();
                dt = Get_Table(TSQL, conn);
                if (dt.Rows.Count > 0)
                {
                    rs = true;
                }
            }
            catch (Exception ex)
            {
                rs = false;
                Common.GhiLog("Exist", ex);
            }
            finally
            {
                if (isClose)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return rs;
        }

        public static bool Exist(string TSQL)
        {
            return Exist(TSQL, objConn);
        }

        public static bool Exist(string TSQL, bool isClose)
        {
            return Exist(TSQL, objConn, isClose);
        }


        public static void BeginTrans(SqlConnection conn,ref SqlTransaction objTrans)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                objTrans = conn.BeginTransaction();
            }
            catch (Exception ex)
            {
                Common.GhiLog("BeginTrans", ex);
            }
        }

        public static void CommitTrans(SqlConnection conn, SqlTransaction objTrans)
        {
            try
            {
                objTrans.Commit();
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Common.GhiLog("CommitTrans", ex);
            }
        }

        public static void RollBackTrans(SqlConnection conn, SqlTransaction objTrans)
        {
            try
            {
                objTrans.Rollback();
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Common.GhiLog("RollBackTrans", ex);
            }
        }

        public static void BeginTrans(ref SqlTransaction objTrans)
        {
            try
            {
                if (objConn.State != ConnectionState.Open)
                {
                    objConn.Open();
                }
                objTrans = objConn.BeginTransaction();
            }
            catch (Exception ex)
            {
                Common.GhiLog("BeginTrans", ex);
            }
        }

        public static void CommitTrans(SqlTransaction objTrans)
        {
            try
            {
                objTrans.Commit();
                if (objConn.State != ConnectionState.Closed)
                {
                    objConn.Close();
                }
            }
            catch (Exception ex)
            {
                Common.GhiLog("CommitTrans", ex);
            }
        }

        public static void RollBackTrans(SqlTransaction objTrans)
        {
            try
            {
                objTrans.Rollback();
                if (objConn.State != ConnectionState.Closed)
                {
                    objConn.Close();
                }
            }
            catch (Exception ex)
            {
                Common.GhiLog("RollBackTrans", ex);
            }
        }
    }
}
